#!/usr/bin/env python

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

Base = declarative_base()

class TbFile(Base):
    __tablename__   = 'tb_file'
    tab_cn_name     = Column(String(100))
    tab_name        = Column(String(100), primary_key=True)
    sys             = Column(String(10))
    source_tab      = Column(String(50))
    source_tab_desc = Column(String(50))

    tb_flds  = relationship("TbFldLenNew", order_by="TbFldLenNew.col_seq")

    def __repr__(self):
        return "<TbFile(tab_cn_name='%s', tab_name='%s',sys='%s',source_tab='%s',source_tab_desc='%s')" % (tab_cn_name, tab_name, sys, source_tab, source_tab_desc)
        
class TbFldLenNew(Base):
    __tablename__    = 'tb_fld_len_new'
    tab_name         = Column(String(100), ForeignKey('tb_file.tab_name'), primary_key=True) 
    col_seq          = Column(Integer)
    sys              = Column(String(10))
    col_cn_name      = Column(String(100))
    col_name         = Column(String(50), primary_key=True) 
    col_type         = Column(String(50))
    pk_flag          = Column(String(1))
    source_tab       = Column(String(100))
    source_col_type  = Column(String(50))
    source_col_desc  = Column(String(100))


engine = create_engine('mysql://root:123456@192.168.56.1/dwmeta',encoding='utf-8', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

for i in session.query(TbFile).order_by(TbFile.tab_name):
    print i.tab_name
    for f in i.tb_flds:
        print f.col_name

    break
